package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oop.data.Answer;
import oop.data.BaseQuestion;
import oop.data.Question;
import oop.db.Database;

public final class AnswerDAO {

	public static List<Answer> fetchByBaseQuestion(BaseQuestion question) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT tblAnswer.ans_id, ans_content, ans_priority, " +
					"correct " +
					"FROM tblAnswer, tblQuestionAnswer " +
					"WHERE tblAnswer.ans_id = tblQuestionAnswer.ans_id " +
					"AND ques_id = ? " +
					"ORDER BY timestamp ASC";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, question.getId());
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Answer> fetchByQuestion(Question question) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT tblAnswer.ans_id, ans_content, ans_priority, correct " +
					"FROM tblAnswer, tblQuestionAnswer, tblSectionQuestionAnswer " +
					"WHERE tblSectionQuestionAnswer.ans_id = tblAnswer.ans_id " +
						"AND tblSectionQuestionAnswer.ans_id = tblQuestionAnswer.ans_id " +
						"AND tblSectionQuestionAnswer.ques_id = tblQuestionAnswer.ques_id " +
						"AND sect_id = ? AND tblSectionQuestionAnswer.ques_id = ? " +
					"ORDER BY timestamp ASC";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, question.getSection().getId());
			stmt.setLong(2, question.getId());
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Answer> fromResultSet(ResultSet rs) throws SQLException {
		List<Answer> answers = new ArrayList<Answer>();
		while (rs.next()) {
			Answer answer = new Answer(rs.getInt(1), rs.getString(2), rs
					.getInt(3), rs.getBoolean(4));
			answers.add(answer);
		}
		return answers;
	}

	public static Answer create(String content, int priority, long authorId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO tblAnswer " +
					"(ans_content, ans_priority, ans_author) " +
					"VALUES (?, ?, ?)";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, content);
			stmt.setInt(2, priority);
			stmt.setLong(3, authorId);
			stmt.executeUpdate();
			
			rs = stmt.getGeneratedKeys();
			rs.next();
			return new Answer(rs.getLong(1), content, priority, false);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
			if (rs != null) rs.close();
		}
		
	}

	public static int drop(long answerId) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblAnswer WHERE ans_id = " + answerId;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static int persist(Answer answer) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "UPDATE tblAnswer SET "
					+ "ans_content=?, ans_priority=? "
					+ "WHERE ans_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, answer.getContent());
			stmt.setInt(2, answer.getPriority());
			stmt.setLong(3, answer.getId());
			return stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
}
